package io.renren.modules.table.service.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.xiaoleilu.hutool.date.DateUtil;
import com.xiaoleilu.hutool.util.CollectionUtil;
import com.xiaoleilu.hutool.util.StrUtil;

import io.renren.common.utils.ItemEnum;
import io.renren.common.utils.ItemEnum2;
import io.renren.common.utils.ItemStrEnum;
import io.renren.common.utils.R;
import io.renren.modules.com.dao.ComAssetDao;
import io.renren.modules.com.dao.ComContractAssetDao;
import io.renren.modules.com.dao.ComContractDao;
import io.renren.modules.com.entity.ComAssetEntity;
import io.renren.modules.com.entity.ComColModel;
import io.renren.modules.com.entity.ComContractAssetEntity;
import io.renren.modules.com.entity.ComContractEntity;
import io.renren.modules.com.entity.ComGroupHeaders;
import io.renren.modules.sys.dao.SysBuildDao;
import io.renren.modules.sys.entity.SysBuildEntity;
import io.renren.modules.table.service.ContractService;

@Service("contractService")
public class ContractServiceImpl implements ContractService {
	@Autowired
	private ComContractDao comContractDao;
	@Autowired
	private ComContractAssetDao comContractAssetDao;
	@Autowired
	private SysBuildDao sysBuildDao;
	@Autowired
	private ComAssetDao comAssetDao;

	@Override
	public R contract(Map<String, Object> map) {
		return this.contractPrivate(map);
	}

	private R contractPrivate(Map<String, Object> map) {
		String yearMonth = (String) map.get("yearMonth");
		String dateMonthS = (String) map.get("dateMonthS");
		String dateMonthE = (String) map.get("dateMonthE");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
		SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy");
		List<Object> l = CollectionUtil.newArrayList("收费项目");
		if (StrUtil.hasBlank(dateMonthS, dateMonthE)) {
			Date dateMonthEs = DateUtil.endOfYear(new Date());
			if (yearMonth.equals("1")) {
				dateMonthE = sdf.format(dateMonthEs);
				Calendar calendar2 = Calendar.getInstance();
				calendar2.setTime(dateMonthEs);
				calendar2.add(Calendar.MONTH, -11);
				dateMonthS = sdf.format(calendar2.getTime());
				while (calendar2.getTime().before(dateMonthEs)
						|| StringUtils.equals(sdf.format(calendar2.getTime()), dateMonthE)) {
					l.add(sdf.format(calendar2.getTime()));
					calendar2.add(Calendar.MONTH, 1);
				}
			} else {
				dateMonthE = sdf2.format(dateMonthEs);
				Calendar calendar2 = Calendar.getInstance();
				calendar2.setTime(dateMonthEs);
				calendar2.add(Calendar.YEAR, 0);
				dateMonthS = sdf2.format(calendar2.getTime());
				while (calendar2.getTime().before(dateMonthEs)
						|| StringUtils.equals(sdf2.format(calendar2.getTime()), dateMonthE)) {
					l.add(sdf2.format(calendar2.getTime()));
					calendar2.add(Calendar.YEAR, 1);
				}
			}
		} else {
			if (yearMonth.equals("1")) {
				Calendar calendar2 = Calendar.getInstance();
				calendar2.setTime(DateUtil.parse(dateMonthS + "-01"));
				while (calendar2.getTime().before(DateUtil.parse(dateMonthE + "-01"))
						|| StringUtils.equals(sdf.format(calendar2.getTime()), dateMonthE)) {
					l.add(sdf.format(calendar2.getTime()));
					calendar2.add(Calendar.MONTH, 1);
				}
			} else {
				Calendar calendar2 = Calendar.getInstance();
				calendar2.setTime(DateUtil.parse(dateMonthS + "-01-01"));
				while (calendar2.getTime().before(DateUtil.parse(dateMonthE + "-01-01"))
						|| StringUtils.equals(sdf2.format(calendar2.getTime()), dateMonthE)) {
					l.add(sdf2.format(calendar2.getTime()));
					calendar2.add(Calendar.YEAR, 1);
				}
			}
		}
		if (yearMonth.equals("1")) {
			dateMonthS += "-01";
			dateMonthE += "-01";
			dateMonthE = DateUtil.format(DateUtil.endOfMonth(DateUtil.parse(dateMonthE)), "yyyy-MM-dd");
		} else {
			dateMonthS += "-01-01";
			dateMonthE += "-12-31";
		}
		map.put("dateMonthS", dateMonthS);
		map.put("dateMonthE", dateMonthE);
		String item = (String) map.get("item");
		if (StringUtils.isBlank(item) || item.equals("点击选择...")) {
			item = "租金, 物业费, 管理费, 公共能耗费, 保证金, 居间服务费, 停车费, 租场费, 其他, 返租租金";
		}
		map.put("item", ItemEnum.getItemList(item.replace(" ", "")));
		map.put("item2", ItemEnum2.getItemList(item.replace(" ", "")));
		List<ComContractEntity> list = comContractDao.queryContractTable(map);
		List<Object> priceList = CollectionUtil.newArrayList("租金");

		List<Object> propertyList = CollectionUtil.newArrayList("物业费");
		List<Object> manageList = CollectionUtil.newArrayList("管理费");
		List<Object> energyList = CollectionUtil.newArrayList("公共能耗费");
		List<Object> bondList = CollectionUtil.newArrayList("保证金");
		List<Object> interList = CollectionUtil.newArrayList("居间服务费");
		List<Object> stopList = CollectionUtil.newArrayList("停车费");
		List<Object> other1List = CollectionUtil.newArrayList("租场费");
		List<Object> otherList = CollectionUtil.newArrayList("其他");
		List<Object> fanzuList = CollectionUtil.newArrayList("返租租金");
		List<Object> zongjiList = CollectionUtil.newArrayList("总计");

		for (int i = 1; i < l.size(); i++) {
			String s = (String) l.get(i);
			for (ComContractEntity entity : list) {
				if (StrUtil.equals(s, entity.getSalesDate())) {
					priceList.add(entity.getTotalPrice() == null ? "--" : entity.getTotalPrice());
					propertyList.add(entity.getTotalProperty() == null ? "--" : entity.getTotalProperty());
					energyList.add(entity.getTotalEnergy() == null ? "--" : entity.getTotalEnergy());
					fanzuList.add(entity.getTotalLprice() == null ? "--" : entity.getTotalLprice());
					manageList.add(entity.getTotalManage() == null ? "--" : entity.getTotalManage());
					bondList.add(entity.getTotalBond() == null ? "--" : entity.getTotalBond());
					interList.add(entity.getTotalInter() == null ? "--" : entity.getTotalInter());
					stopList.add(entity.getTotalStop() == null ? "--" : entity.getTotalStop());
					other1List.add(entity.getTotalOther1() == null ? "--" : entity.getTotalOther1());
					otherList.add(entity.getTotalOther() == null ? "--" : entity.getTotalOther());
					zongjiList.add(entity.getTotalMoney());
				}
			}
			if (priceList.size() < i + 1) {
				priceList.add("--");
			}
			if (propertyList.size() < i + 1) {
				propertyList.add("--");
			}
			if (energyList.size() < i + 1) {
				energyList.add("--");
			}
			if (fanzuList.size() < i + 1) {
				fanzuList.add("--");
			}
			if (manageList.size() < i + 1) {
				manageList.add("--");
			}
			if (bondList.size() < i + 1) {
				bondList.add("--");
			}
			if (interList.size() < i + 1) {
				interList.add("--");
			}
			if (stopList.size() < i + 1) {
				stopList.add("--");
			}
			if (other1List.size() < i + 1) {
				other1List.add("--");
			}
			if (otherList.size() < i + 1) {
				otherList.add("--");
			}
			if (zongjiList.size() < i + 1) {
				zongjiList.add("--");
			}
		}
		List<Object> listss = CollectionUtil.newArrayList(l, priceList, propertyList, manageList, energyList, bondList,
				interList, stopList, other1List, otherList, fanzuList, zongjiList);
		if (!item.contains("租金")) {
			listss.remove(priceList);
		}
		if (!item.contains("物业费")) {
			listss.remove(propertyList);
		}
		if (!item.contains("管理费")) {
			listss.remove(manageList);
		}
		if (!item.contains("公共能耗费")) {
			listss.remove(energyList);
		}
		if (!item.contains("保证金")) {
			listss.remove(bondList);
		}
		if (!item.contains("居间服务费")) {
			listss.remove(interList);
		}
		if (!item.contains("停车费")) {
			listss.remove(stopList);
		}
		if (!item.contains("租场费")) {
			listss.remove(other1List);
		}
		if (!item.contains("其他")) {
			listss.remove(otherList);
		}
		if (!item.contains("返租租金")) {
			listss.remove(fanzuList);
		}
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(new Date());
		calendar.set(Calendar.MONTH, calendar.getActualMinimum(Calendar.MONTH));
		String startMonth = sdf.format(calendar.getTime());
		calendar.set(Calendar.MONTH, calendar.getActualMaximum(Calendar.MONTH));
		String endMonth = sdf.format(calendar.getTime());

		String startYear = sdf2.format(DateUtil.beginOfYear(new Date()));
		String endYear = sdf2.format(DateUtil.endOfYear(new Date()));

		return R.ok().put("list", listss).put("startMonth", startMonth).put("endMonth", endMonth)
				.put("startYear", startYear).put("endYear", endYear);
	}

	@Override
	public R contractDetailInit(Map<String, Object> params) {
		return this.contractDetailInitPrivate(params);
	}

	private R contractDetailInitPrivate(Map<String, Object> params) {
		Map<String, Object> map = new HashMap<String, Object>();
		String yearMonth = (String) params.get("yearMonth");
		String dateMonthS = (String) params.get("dateMonthS");
		String dateMonthE = (String) params.get("dateMonthE");
		SimpleDateFormat sdf = null;
		if (StrUtil.isBlank(yearMonth)) {
			yearMonth = "1";
			params.put("yearMonth", "1");
		}
		if (yearMonth.equals("1")) {
			sdf = new SimpleDateFormat("yyyy-MM");
		} else {
			sdf = new SimpleDateFormat("yyyy");
		}
		Date dateMonthEs = DateUtil.endOfYear(new Date());
		Calendar cal = Calendar.getInstance();
		Calendar calendar2 = Calendar.getInstance();
		if (StrUtil.hasBlank(dateMonthS, dateMonthE)) {
			if (yearMonth.equals("1")) {
				// 初始化分摊结束时间
				dateMonthE = sdf.format(dateMonthEs);
				cal.setTime(dateMonthEs);
				calendar2 = Calendar.getInstance();
				calendar2.setTime(dateMonthEs);
				calendar2.add(Calendar.MONTH, -11);
				dateMonthS = sdf.format(calendar2.getTime());

			} else {
				dateMonthE = sdf.format(dateMonthEs);
				cal.setTime(dateMonthEs);
				calendar2 = Calendar.getInstance();
				calendar2.setTime(dateMonthEs);
				calendar2.add(Calendar.YEAR, 0);
				dateMonthS = sdf.format(calendar2.getTime());
			}
		} else {
			if (yearMonth.equals("1")) {
				dateMonthS += "-01";
				dateMonthE = DateUtil.format(DateUtil.endOfMonth(DateUtil.parse(dateMonthE + "-01")), "yyyy-MM-dd");
				dateMonthEs = DateUtil.parse(dateMonthE);
				calendar2.setTime(DateUtil.parse(dateMonthS));
			} else {
				dateMonthS += "-01-01";
				dateMonthE += "-12-31";
				dateMonthEs = DateUtil.parse(dateMonthE);
				calendar2.setTime(DateUtil.parse(dateMonthS));
			}
		}
		String item = (String) params.get("item");
		if (StringUtils.isBlank(item) || item.equals("点击选择...")) {
			item = "租金, 物业费, 管理费, 公共能耗费, 保证金, 居间服务费, 停车费, 租场费, 其他, 返租租金";
		}
		item = item.replace(" ", "");
		// 初始化分摊结束时间
		map.put("dateMonthE", dateMonthE);
		// 初始化分摊开始时间
		map.put("dateMonthS", sdf.format(calendar2.getTime()));
		System.out.println(calendar2.getTime());
		// 初始化jqgrid表头列名
		map.put("colNames", initializationColNames(item, calendar2.getTime(), dateMonthEs, sdf, yearMonth));
		// 初始化jqgrid表头列名属性
		map.put("colModel", initializationColModel(item, calendar2.getTime(), dateMonthEs, sdf, yearMonth));
		map.put("groupHeaders", initializationGroupHeaders(item, yearMonth, calendar2.getTime(), dateMonthEs));
		map.put("start", calendar2.getTime());
		map.put("end", dateMonthEs);
		map.put("yearMonth", yearMonth);
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(new Date());
		calendar.set(Calendar.MONTH, calendar.getActualMinimum(Calendar.MONTH));
		String startMonth = DateUtil.format(calendar.getTime(), "yyyy-MM");
		calendar.set(Calendar.MONTH, calendar.getActualMaximum(Calendar.MONTH));
		String endMonth = DateUtil.format(calendar.getTime(), "yyyy-MM");
		String startYear = DateUtil.format(DateUtil.beginOfYear(new Date()), "yyyy");
		String endYear = DateUtil.format(DateUtil.endOfYear(new Date()), "yyyy");
		map.put("startMonth", startMonth);
		map.put("endMonth", endMonth);
		map.put("startYear", startYear);
		map.put("endYear", endYear);
		return R.ok(map);
	}

	private List<ComGroupHeaders> initializationGroupHeaders(String item, String yearMonth, Date sd, Date ed) {
		List<ComGroupHeaders> comGroupHeadersList = new ArrayList<ComGroupHeaders>();
		List<String> colNames2 = CollectionUtil.newArrayList(item.split(","));
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(sd);
		SimpleDateFormat sdf = null;
		ComGroupHeaders comGroupHeaders = null;
		if (StringUtils.equals("1", yearMonth)) {
			sdf = new SimpleDateFormat("yyyy-MM");
			while (calendar.getTime().before(ed)
					|| StringUtils.equals(sdf.format(calendar.getTime()), sdf.format(ed))) {
				comGroupHeaders = new ComGroupHeaders();
				comGroupHeaders.setStartColumnName(
						ItemStrEnum.getItem(colNames2.get(0)).getValue() + sdf.format(calendar.getTime()));
				comGroupHeaders.setNumberOfColumns(colNames2.size() + 1);
				comGroupHeaders.setTitleText(sdf.format(calendar.getTime()));
				calendar.add(Calendar.MONTH, 1);
				comGroupHeadersList.add(comGroupHeaders);
			}
		}
		if (StringUtils.equals("2", yearMonth)) {
			sdf = new SimpleDateFormat("yyyy");
			while (calendar.getTime().before(ed)
					|| StringUtils.equals(sdf.format(calendar.getTime()), sdf.format(ed))) {
				comGroupHeaders = new ComGroupHeaders();
				comGroupHeaders.setStartColumnName(
						ItemStrEnum.getItem(colNames2.get(0)).getValue() + sdf.format(calendar.getTime()));
				comGroupHeaders.setNumberOfColumns(colNames2.size() + 1);
				comGroupHeaders.setTitleText(sdf.format(calendar.getTime()));
				calendar.add(Calendar.YEAR, 1);
				comGroupHeadersList.add(comGroupHeaders);
			}
		}
		return comGroupHeadersList;
	}

	private List<String> initializationColNames(String item, Date dateMonthS, Date dateMonthE, SimpleDateFormat sdf,
			String yearMonth) {
		List<String> colNames = CollectionUtil.newArrayList("楼号", "房号", "合同号");
		List<String> colNames2 = CollectionUtil.newArrayList(item.split(","));
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(dateMonthS);
		while (calendar.getTime().before(dateMonthE)
				|| StringUtils.equals(sdf.format(calendar.getTime()), sdf.format(dateMonthE))) {
			colNames.addAll(colNames2);
			colNames.add("总计");
			if (yearMonth.equals("1")) {
				calendar.add(Calendar.MONTH, 1);
			} else {
				calendar.add(Calendar.YEAR, 1);
			}
		}
		return colNames;
	}

	private List<ComColModel> initializationColModel(String item, Date dateMonthS, Date dateMonthE,
			SimpleDateFormat sdf, String yearMonth) {
		List<ComColModel> colModelList = new ArrayList<ComColModel>();
		// 楼号
		ComColModel comColModel = new ComColModel();
		comColModel.setName("buildIds");
		comColModel.setWidth(100);
		comColModel.setAlign("center");
		comColModel.setFrozen(true);
		colModelList.add(comColModel);
		// 房号
		comColModel = new ComColModel();
		comColModel.setName("assetIds");
		comColModel.setWidth(100);
		comColModel.setAlign("center");
		comColModel.setFrozen(true);
		colModelList.add(comColModel);
		// 合同号
		comColModel = new ComColModel();
		comColModel.setName("contractNo");
		comColModel.setWidth(100);
		comColModel.setAlign("center");
		comColModel.setFrozen(true);
		colModelList.add(comColModel);
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(dateMonthS);
		while (calendar.getTime().before(dateMonthE)
				|| StringUtils.equals(sdf.format(calendar.getTime()), sdf.format(dateMonthE))) {
			if (item.contains("租金")) {
				comColModel = new ComColModel();
				comColModel.setName("zujin" + sdf.format(calendar.getTime()));
				comColModel.setWidth(80);
				comColModel.setAlign("center");
				colModelList.add(comColModel);
			}
			if (item.contains("物业费")) {
				comColModel = new ComColModel();
				comColModel.setName("wuye" + sdf.format(calendar.getTime()));
				comColModel.setWidth(80);
				comColModel.setAlign("center");
				colModelList.add(comColModel);
			}
			if (item.contains("管理费")) {
				comColModel = new ComColModel();
				comColModel.setName("guanli" + sdf.format(calendar.getTime()));
				comColModel.setWidth(80);
				comColModel.setAlign("center");
				colModelList.add(comColModel);
			}
			if (item.contains("公共能耗费")) {
				comColModel = new ComColModel();
				comColModel.setName("nenghao" + sdf.format(calendar.getTime()));
				comColModel.setWidth(90);
				comColModel.setAlign("center");
				colModelList.add(comColModel);
			}
			if (item.contains("保证金")) {
				comColModel = new ComColModel();
				comColModel.setName("baozheng" + sdf.format(calendar.getTime()));
				comColModel.setWidth(80);
				comColModel.setAlign("center");
				colModelList.add(comColModel);
			}
			if (item.contains("居间服务费")) {
				comColModel = new ComColModel();
				comColModel.setName("jujian" + sdf.format(calendar.getTime()));
				comColModel.setWidth(90);
				comColModel.setAlign("center");
				colModelList.add(comColModel);
			}
			if (item.contains("停车费")) {
				comColModel = new ComColModel();
				comColModel.setName("tingche" + sdf.format(calendar.getTime()));
				comColModel.setWidth(80);
				comColModel.setAlign("center");
				colModelList.add(comColModel);
			}
			if (item.contains("租场费")) {
				comColModel = new ComColModel();
				comColModel.setName("qita1" + sdf.format(calendar.getTime()));
				comColModel.setWidth(80);
				comColModel.setAlign("center");
				colModelList.add(comColModel);
			}
			if (item.contains("其他")) {
				comColModel = new ComColModel();
				comColModel.setName("qita" + sdf.format(calendar.getTime()));
				comColModel.setWidth(80);
				comColModel.setAlign("center");
				colModelList.add(comColModel);
			}
			if (item.contains("返租租金")) {
				comColModel = new ComColModel();
				comColModel.setName("fanzu" + sdf.format(calendar.getTime()));
				comColModel.setWidth(80);
				comColModel.setAlign("center");
				colModelList.add(comColModel);
			}
			comColModel = new ComColModel();
			comColModel.setName("zongji" + sdf.format(calendar.getTime()));
			comColModel.setWidth(80);
			comColModel.setAlign("center");
			colModelList.add(comColModel);
			if (yearMonth.equals("1")) {
				calendar.add(Calendar.MONTH, 1);
			} else {
				calendar.add(Calendar.YEAR, 1);
			}
		}
		return colModelList;
	}

	@Override
	public List<Map<String, Object>> contractDetail(Map<String, Object> map) {
		return this.contractDetailPrivate(map);
	}

	private List<Map<String, Object>> contractDetailPrivate(Map<String, Object> map) {
		List<Map<String, Object>> chinaJoy = new ArrayList<Map<String, Object>>();
		// 准备查询数据
		String yearMonth = (String) map.get("yearMonth");
		String dateMonthE = (String) map.get("dateMonthE");
		String dateMonthS = (String) map.get("dateMonthS");
		if (StrUtil.isBlank(yearMonth)) {
			yearMonth = "1";
			map.put("yearMonth", "1");
		}

		SimpleDateFormat sdf = null;
		if (yearMonth.equals("1")) {
			sdf = new SimpleDateFormat("yyyy-MM");
		} else {
			sdf = new SimpleDateFormat("yyyy");
		}
		Date dateMonthEs = new Date();
		Calendar cal = Calendar.getInstance();
		Calendar cal2 = Calendar.getInstance();
		Calendar calendar2 = Calendar.getInstance();
		if (StrUtil.hasBlank(dateMonthS, dateMonthE)) {
			if (yearMonth.equals("1")) {
				// 初始化分摊结束时间
				dateMonthE = sdf.format(dateMonthEs);
				cal.setTime(dateMonthEs);
				calendar2 = Calendar.getInstance();
				calendar2.setTime(dateMonthEs);
				calendar2.add(Calendar.MONTH, -11);
				dateMonthS = sdf.format(calendar2.getTime());
				map.put("dateMonthS", dateMonthS + "-01");
				map.put("dateMonthE",
						DateUtil.format(DateUtil.endOfMonth(DateUtil.parse(dateMonthE + "-01")), "yyyy-MM-dd"));
			} else {
				dateMonthE = sdf.format(dateMonthEs);
				cal.setTime(dateMonthEs);
				calendar2 = Calendar.getInstance();
				calendar2.setTime(dateMonthEs);
				calendar2.add(Calendar.YEAR, 0);
				dateMonthS = sdf.format(calendar2.getTime());
				map.put("dateMonthS", dateMonthS + "-01-01");
				map.put("dateMonthE", dateMonthE + "-12-31");
			}
		} else {
			if (yearMonth.equals("1")) {

				dateMonthS += "-01";
				dateMonthE = DateUtil.format(DateUtil.endOfMonth(DateUtil.parse(dateMonthE + "-01")), "yyyy-MM-dd");
				cal.setTime(DateUtil.parse(dateMonthE));
				calendar2.setTime(DateUtil.parse(dateMonthS));
				map.put("dateMonthS", dateMonthS);
				map.put("dateMonthE", dateMonthE);
			} else {
				dateMonthS += "-01-01";
				dateMonthE += "-12-31";
				cal.setTime(DateUtil.parse(dateMonthE));
				calendar2.setTime(DateUtil.parse(dateMonthS));
				map.put("dateMonthS", dateMonthS);
				map.put("dateMonthE", dateMonthE);
			}
		}
		cal2.setTime(calendar2.getTime());
		String item = (String) map.get("item");
		if (StringUtils.isBlank(item) || item.equals("点击选择...")) {
			item = "租金, 物业费, 管理费, 公共能耗费, 保证金, 居间服务费, 停车费 , 租场费, 其他, 返租租金";
		}
		map.put("item", ItemEnum.getItemList(item.replace(" ", "")));
		List<String> colNames2 = CollectionUtil.newArrayList(item.split(","));
		List<ComContractEntity> list = comContractDao.queryContractDetailTable(map);

		for (ComContractEntity entity : list) {
			Map<String, Object> maps = new HashMap<>();
			maps.put("contractId", entity.getContractId());
			List<ComContractAssetEntity> assetList = comContractAssetDao.queryList(maps);
			String assetIds = "";
			String buildIds = "";
			for (ComContractAssetEntity comContractAssetEntity : assetList) {
				ComAssetEntity asset = comAssetDao.queryObject(comContractAssetEntity.getAssetId());
				if (asset != null) {
					assetIds += ";" + asset.getRoomNum();
					SysBuildEntity build = sysBuildDao.queryObject(asset.getBuildId());
					if (build != null) {
						buildIds += ";" + build.getBuildName();
					}
				}
			}
			if (StringUtils.isNotBlank(buildIds)) {
				buildIds = buildIds.substring(1);
			}
			if (StringUtils.isNotBlank(assetIds)) {
				assetIds = assetIds.substring(1);
			}

			Map<String, Object> chinaJoyMap = new HashMap<String, Object>();
			chinaJoyMap.put("buildIds", buildIds);
			chinaJoyMap.put("assetIds", assetIds);
			chinaJoyMap.put("contractNo", entity.getContractNo());
			int count = 3;
			while (calendar2.before(cal) || StringUtils.equals(sdf.format(calendar2.getTime()), dateMonthE)) {

				if (entity.getSalesDate().equals(sdf.format(calendar2.getTime()))) {

					if (item.contains("租金")) {
						chinaJoyMap.put("zujin" + sdf.format(calendar2.getTime()),
								entity.getTotalPrice() != null ? entity.getTotalPrice() : "--");
					}
					if (item.contains("物业费")) {
						chinaJoyMap.put("wuye" + sdf.format(calendar2.getTime()),
								entity.getTotalProperty() != null ? entity.getTotalProperty() : "--");
					}
					if (item.contains("管理费")) {
						chinaJoyMap.put("guanli" + sdf.format(calendar2.getTime()),
								entity.getTotalManage() != null ? entity.getTotalManage() : "--");
					}
					if (item.contains("公共能耗费")) {
						chinaJoyMap.put("nenghao" + sdf.format(calendar2.getTime()),
								entity.getTotalEnergy() != null ? entity.getTotalEnergy() : "--");
					}
					if (item.contains("保证金")) {
						chinaJoyMap.put("baozheng" + sdf.format(calendar2.getTime()),
								entity.getTotalBond() != null ? entity.getTotalBond() : "--");
					}
					if (item.contains("居间服务费")) {
						chinaJoyMap.put("jujian" + sdf.format(calendar2.getTime()),
								entity.getTotalInter() != null ? entity.getTotalInter() : "--");
					}
					if (item.contains("停车费")) {
						chinaJoyMap.put("tingche" + sdf.format(calendar2.getTime()),
								entity.getTotalStop() != null ? entity.getTotalStop() : "--");
					}
					if (item.contains("租场费")) {
						chinaJoyMap.put("qita1" + sdf.format(calendar2.getTime()),
								entity.getTotalOther1() != null ? entity.getTotalOther1() : "--");
					}
					if (item.contains("其他")) {
						chinaJoyMap.put("qita" + sdf.format(calendar2.getTime()),
								entity.getTotalOther() != null ? entity.getTotalOther() : "--");
					}
					if (item.contains("返租租金")) {
						chinaJoyMap.put("fanzu" + sdf.format(calendar2.getTime()),
								entity.getTotalLprice() != null ? entity.getTotalLprice() : "--");
					}

					chinaJoyMap.put("zongji" + sdf.format(calendar2.getTime()),
							entity.getTotalMoney() != null ? entity.getTotalMoney() : "--");
				}

				if (chinaJoyMap.size() <= count) {

					if (item.contains("租金")) {
						chinaJoyMap.put("zujin" + sdf.format(calendar2.getTime()), "--");
					}
					if (item.contains("物业费")) {
						chinaJoyMap.put("wuye" + sdf.format(calendar2.getTime()), "--");
					}
					if (item.contains("管理费")) {
						chinaJoyMap.put("guanli" + sdf.format(calendar2.getTime()), "--");
					}
					if (item.contains("公共能耗费")) {
						chinaJoyMap.put("nenghao" + sdf.format(calendar2.getTime()), "--");
					}
					if (item.contains("保证金")) {
						chinaJoyMap.put("baozheng" + sdf.format(calendar2.getTime()), "--");
					}
					if (item.contains("居间服务费")) {
						chinaJoyMap.put("jujian" + sdf.format(calendar2.getTime()), "--");
					}
					if (item.contains("停车费")) {
						chinaJoyMap.put("tingche" + sdf.format(calendar2.getTime()), "--");
					}
					if (item.contains("租场费")) {
						chinaJoyMap.put("qita1" + sdf.format(calendar2.getTime()), "--");
					}
					if (item.contains("其他")) {
						chinaJoyMap.put("qita" + sdf.format(calendar2.getTime()), "--");
					}
					if (item.contains("返租租金")) {
						chinaJoyMap.put("fanzu" + sdf.format(calendar2.getTime()), "--");
					}
					chinaJoyMap.put("zongji" + sdf.format(calendar2.getTime()), "--");
				}
				count += colNames2.size() + 1;
				if (yearMonth.equals("1")) {
					calendar2.add(Calendar.MONTH, 1);
				} else {
					calendar2.add(Calendar.YEAR, 1);
				}
			}
			chinaJoy.add(chinaJoyMap);
			calendar2.setTime(cal2.getTime());
		}

		return chinaJoy;
	}

	@Override
	public int contractDetailTotal(Map<String, Object> param) {
		return this.contractDetailTotalPrivate(param);
	}

	private int contractDetailTotalPrivate(Map<String, Object> param) {
		String yearMonth = (String) param.get("yearMonth");
		String dateMonthS = (String) param.get("dateMonthS");
		String dateMonthE = (String) param.get("dateMonthE");
		SimpleDateFormat sdf = null;
		if (StrUtil.isBlank(yearMonth)) {
			yearMonth = "1";
			param.put("yearMonth", "1");
		}
		if (yearMonth.equals("1")) {
			sdf = new SimpleDateFormat("yyyy-MM");
		} else {
			sdf = new SimpleDateFormat("yyyy");

		}

		Date dateMonthEs = new Date();
		Calendar cal = Calendar.getInstance();
		Calendar calendar2 = Calendar.getInstance();
		if (StrUtil.hasBlank(dateMonthS, dateMonthE)) {
			if (yearMonth.equals("1")) {
				// 初始化分摊结束时间
				dateMonthE = sdf.format(dateMonthEs);
				cal.setTime(dateMonthEs);
				calendar2 = Calendar.getInstance();
				calendar2.setTime(dateMonthEs);
				calendar2.add(Calendar.MONTH, -5);
				dateMonthS = sdf.format(calendar2.getTime());

			} else {
				dateMonthE = sdf.format(dateMonthEs);
				cal.setTime(dateMonthEs);
				calendar2 = Calendar.getInstance();
				calendar2.setTime(dateMonthEs);
				calendar2.add(Calendar.YEAR, -5);
				dateMonthS = sdf.format(calendar2.getTime());
			}
		}

		// 初始化分摊结束时间
		param.put("dateMonthE", dateMonthE);
		// 初始化分摊开始时间
		param.put("dateMonthS", sdf.format(calendar2.getTime()));
		int total = comContractDao.queryContractDetailDistinctNum(param);
		return total;
	}

	@Override
	public void contractExport(String title, List<Object> chinaJoy, OutputStream out) {
		this.contractExportPrivate(title, chinaJoy, out);
	}

	@SuppressWarnings({ "deprecation", "unchecked" })
	private void contractExportPrivate(String title, List<Object> chinaJoy, OutputStream out) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);
		for (short i = 0; i < chinaJoy.size(); i++) {
			List<Object> l = (List<Object>) chinaJoy.get(i);
			HSSFRow row = sheet.createRow(i);
			for (int j = 0; j < l.size(); j++) {
				HSSFCell cell = row.createCell(j);
				if (i == 0) {
					cell.setCellStyle(style);
				}
				Object o = l.get(j);
				if (o instanceof Double) {
					cell.setCellValue((Double) l.get(j));
				} else {
					cell.setCellValue((String) l.get(j));
				}
			}
		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void contractDetailExport(String title, Map<String, Object> param, OutputStream out) {
		this.contractDetailExportPrivate(title, param, out);
	}

	@SuppressWarnings({ "unchecked", "deprecation" })
	private void contractDetailExportPrivate(String title, Map<String, Object> param, OutputStream out) {
		String item = (String) param.get("item");
		if (StringUtils.isBlank(item) || item.equals("点击选择...")) {
			item = "租金, 物业费, 管理费, 公共能耗费, 保证金, 居间服务费, 停车费 , 租场费, 其他, 返租租金";
		}
		Map<String, Object> params = new HashMap<>();
		params = param;
		R r = this.contractDetailInit(params);
		List<Map<String, Object>> list = this.contractDetail(param);
		List<String> colNameList = (List<String>) r.get("colNames");
		Date sd = (Date) r.get("start");
		Date ed = (Date) r.get("end");
		String yearMonth = (String) r.get("yearMonth");
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		CellRangeAddress cra = new CellRangeAddress(0, 1, 0, 0);
		CellRangeAddress cra1 = new CellRangeAddress(0, 1, 1, 1);
		CellRangeAddress cra2 = new CellRangeAddress(0, 1, 2, 2);
		RegionUtil.setBorderBottom(1, cra, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(1, cra, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(1, cra, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(1, cra, sheet, workbook); // 上边框
		RegionUtil.setBorderBottom(1, cra1, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(1, cra1, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(1, cra1, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(1, cra1, sheet, workbook); // 上边框
		RegionUtil.setBorderBottom(1, cra2, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(1, cra2, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(1, cra2, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(1, cra2, sheet, workbook); // 上边框
		sheet.addMergedRegion(cra);
		sheet.addMergedRegion(cra1);
		sheet.addMergedRegion(cra2);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 设置这些样式
		style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
		style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
		style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
		style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);
		HSSFRow row = null;
		row = sheet.createRow(0);
		HSSFCell cell = null;
		for (int i = 0; i < 3; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(colNameList.get(i));
		}
		List<String> colNames2 = CollectionUtil.newArrayList(item.split(","));
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(sd);
		SimpleDateFormat sdf = null;
		int count = 3;
		if (StringUtils.equals("1", yearMonth)) {
			sdf = new SimpleDateFormat("yyyy-MM");
			while (calendar.getTime().before(ed)
					|| StringUtils.equals(sdf.format(calendar.getTime()), sdf.format(ed))) {
				cell = row.createCell(count);
				cell.setCellStyle(style);
				CellRangeAddress cra3 = new CellRangeAddress(0, 0, count, count + colNames2.size());
				RegionUtil.setBorderBottom(2, cra3, sheet, workbook); // 下边框
				RegionUtil.setBorderLeft(2, cra3, sheet, workbook); // 左边框
				RegionUtil.setBorderRight(2, cra3, sheet, workbook); // 有边框
				RegionUtil.setBorderTop(2, cra3, sheet, workbook); // 上边框
				sheet.addMergedRegion(cra3);
				cell.setCellValue(sdf.format(calendar.getTime()));
				count += colNames2.size() + 1;
				calendar.add(Calendar.MONTH, 1);
			}
		}
		if (StringUtils.equals("2", yearMonth)) {
			sdf = new SimpleDateFormat("yyyy");
			while (calendar.getTime().before(ed)
					|| StringUtils.equals(sdf.format(calendar.getTime()), sdf.format(ed))) {
				cell = row.createCell(count);
				cell.setCellStyle(style);
				CellRangeAddress cra3 = new CellRangeAddress(0, 0, count, count + colNames2.size());
				RegionUtil.setBorderBottom(1, cra3, sheet, workbook); // 下边框
				RegionUtil.setBorderLeft(1, cra3, sheet, workbook); // 左边框
				RegionUtil.setBorderRight(1, cra3, sheet, workbook); // 有边框
				RegionUtil.setBorderTop(1, cra3, sheet, workbook); // 上边框
				sheet.addMergedRegion(cra3);
				cell.setCellValue(sdf.format(calendar.getTime()));
				count += colNames2.size() + 1;
				calendar.add(Calendar.YEAR, 1);
			}
		}
		row = sheet.createRow(1);
		for (int i = 0; i < 3; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(style);
		}
		for (int i = 3; i < colNameList.size(); i++) {
			cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(colNameList.get(i));
		}
		int x = 2;
		for (Map<String, Object> m : list) {
			Calendar calendar2 = Calendar.getInstance();
			calendar2.setTime(sd);
			row = sheet.createRow(x);
			cell = row.createCell(0);
			cell.setCellStyle(style2);
			cell.setCellValue((String) m.get("buildIds"));
			cell = row.createCell(1);
			cell.setCellStyle(style2);
			cell.setCellValue((String) m.get("assetIds"));
			cell = row.createCell(2);
			cell.setCellStyle(style2);
			cell.setCellValue((String) m.get("contractNo"));
			int index = 3;
			if (StringUtils.equals("1", yearMonth)) {
				sdf = new SimpleDateFormat("yyyy-MM");
				while (calendar2.getTime().before(ed)
						|| StringUtils.equals(sdf.format(calendar2.getTime()), sdf.format(ed))) {
					this.setCellValue(item, style2, index, row, m, sdf.format(calendar2.getTime()));
					index += colNames2.size() + 1;
					calendar2.add(Calendar.MONTH, 1);
				}
			}
			if (StringUtils.equals("2", yearMonth)) {
				sdf = new SimpleDateFormat("yyyy");
				while (calendar2.getTime().before(ed)
						|| StringUtils.equals(sdf.format(calendar2.getTime()), sdf.format(ed))) {
					this.setCellValue(item, style2, index, row, m, sdf.format(calendar2.getTime()));
					index += colNames2.size() + 1;
					calendar2.add(Calendar.YEAR, 1);
				}
			}
			x++;
		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private void setCellValue(String item, HSSFCellStyle style, int index, HSSFRow row, Map<String, Object> m,
			String date) {
		HSSFCell cell = null;
		Object o = null;
		if (item.contains("租金")) {
			cell = row.createCell(index);
			cell.setCellStyle(style);
			o = m.get("zujin" + date);
			this.setValue(cell, m, o);
			index++;
		}
		if (item.contains("物业费")) {
			cell = row.createCell(index);
			cell.setCellStyle(style);
			o = m.get("wuye" + date);
			this.setValue(cell, m, o);
			index++;
		}
		if (item.contains("管理费")) {
			cell = row.createCell(index);
			cell.setCellStyle(style);
			o = m.get("guanli" + date);
			this.setValue(cell, m, o);
			index++;
		}
		if (item.contains("公共能耗费")) {
			cell = row.createCell(index);
			cell.setCellStyle(style);
			o = m.get("nenghao" + date);
			this.setValue(cell, m, o);
			index++;
		}
		if (item.contains("保证金")) {
			cell = row.createCell(index);
			cell.setCellStyle(style);
			o = m.get("baozheng" + date);
			this.setValue(cell, m, o);
			index++;
		}
		if (item.contains("居间服务费")) {
			cell = row.createCell(index);
			cell.setCellStyle(style);
			o = m.get("jujian" + date);
			this.setValue(cell, m, o);
			index++;
		}
		if (item.contains("停车费")) {
			cell = row.createCell(index);
			cell.setCellStyle(style);
			o = m.get("tingche" + date);
			this.setValue(cell, m, o);
			index++;
		}
		if (item.contains("租场费")) {
			cell = row.createCell(index);
			cell.setCellStyle(style);
			o = m.get("qita1" + date);
			this.setValue(cell, m, o);
			index++;
		}
		if (item.contains("其他")) {
			cell = row.createCell(index);
			cell.setCellStyle(style);
			o = m.get("qita" + date);
			this.setValue(cell, m, o);
			index++;
		}
		if (item.contains("返租租金")) {
			cell = row.createCell(index);
			cell.setCellStyle(style);
			o = m.get("fanzu" + date);
			this.setValue(cell, m, o);
			index++;
		}
		cell = row.createCell(index);
		cell.setCellStyle(style);
		o = m.get("zongji" + date);
		this.setValue(cell, m, o);
		index++;
	}

	private void setValue(HSSFCell cell, Map<String, Object> m, Object o) {
		if (o instanceof Double) {
			cell.setCellValue((Double) o);
		} else {
			cell.setCellValue((String) o);
		}
	}

}
